﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_GetReceiverInClass]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_GetReceiverInClass];
GO
CREATE PROCEDURE [dbo].[sproc_GetReceiverInClass] 
    @ClassID int
/*

============================================================
功能:    得到在组（类）里面可以发送邮件的人员列表
参数:
    @ClassID int        :    组（类）ID
    
============================================================

*/
AS

SET  NOCOUNT ON
--如果小于等于0，则为所有在职人员
If @ClassID <=0 
    SELECT * FROM uds_staff WHERE dimission=0
ELSE
--所有组（类）成员和订阅着
    SELECT * 
        FROM uds_staff 
        WHERE staff_id IN (SELECT staff_id 
                        FROM uds_staff_in_team 
                        WHERE team_id = @ClassID) and dimission=0
    UNION
    SELECT * 
        FROM uds_staff 
        WHERE staff_id IN (SELECT staff_id 
                        FROM UDS_Subscription 
                        WHERE classid = @classid) and dimission=0

SET NOCOUNT OFF